using System;
using System.Data;
using System.Configuration;
using System.Data.OracleClient;
using System.Diagnostics;
using System.IO;
using AdCompose;

/// <summary>
/// Summary description for DataGateway
/// </summary>
public class DataGateway
{
    private string connectionString;
     //= "Data Source=db001;Persist Security Info=True;User ID=davinci;Password=davinci;Unicode=True";
    private OracleConnection connection;
    private OracleCommand command;
    private OracleDataAdapter adapter;
    ConnectionState constate;

    private string strMsg;
    private string strSql = "";
    
    private DateTime m_dtPub;

    private string m_target = "";
    private string m_template = "";
    private string m_script = "";
    private string m_src = "";

    public DataGateway(string connstring)
    {
        connectionString = connstring;

        connection = new OracleConnection(connectionString);
        command = connection.CreateCommand();
        adapter = new OracleDataAdapter(command);

        strMsg = "";

        try
        {
            connection.Open();
            constate = connection.State;

            constate = ConnectionState.Closed;
        }
        catch (Exception error)
        {
            Trace.WriteLine(String.Format("{0} {1:s}", DateTime.Now, error.Message));
            strMsg = error.Message;
        }
    }

    public string GetErrorMsg()
    {
        return strMsg;
    }

    public void Dispose()
    {
        adapter.Dispose();
        command.Dispose();
        connection.Close();  // explicit close connection with db
        connection.Dispose();
    }

    public string getTemplate()
    {
        return m_template;
    }

    public string getXml()
    {
        return m_target;
    }
    public string getTargetDirectory()
    {
        return m_src;
    }
    public string getScript()
    {
        return m_script;
    }

    private DataSet getSQLResult(string sql)
    {
        command.Parameters.Clear();
        command.CommandType = CommandType.Text;
        command.CommandText = sql;
        DataSet ds = new DataSet();
        try
        {
            adapter.Fill(ds);
        }
        catch (Exception error)
        {
            Trace.WriteLine(String.Format("{0} {1:s} / {2}", DateTime.Now, error.Message, command.CommandText));
            strMsg = error.Message;
        }

        return ds;
    }

    
    public int GetAdvWithoutPosition(DateTime dtPub, int pubid)
    {
        strSql = String.Format("select count(1) as total from vf_adv_nullpos a where ai_date = date '{0:s}' and ai_pubid = {1:d} ", dtPub.ToString("yyyy-MM-dd"), pubid);
        int totalads = 0;
        return totalads;

        DataSet ds = getSQLResult(strSql);

        if (ds.Tables[0].Rows.Count > 0)
        {
            totalads = Convert.ToInt32(ds.Tables[0].Rows[0]["total"]);
        }
        return totalads;
    }

    public DataSet GetPages_NewsCode(string newscode, DateTime dtDate,string pubid)
    {

        strSql = String.Format("select pageno as ctl_text , a.* from vf_advcompose a where  ai_code like '%{0}%' and ai_date = date '{1}' and ai_pubid in ({2}) order by isincludecode ", newscode, dtDate.ToString("yyyy-MM-dd"), pubid);

        return getSQLResult(strSql);
    }

    public DataSet GetPages(DateTime dtPub, string pubid)
    {
        m_dtPub = dtPub;

        strSql = String.Format("select pageno as ctl_text , a.* from vf_advcompose a where ai_date = date '{0:s}' and ai_pubid in ({1:s}) order by pageno ", dtPub.ToString("yyyy-MM-dd"), pubid);

        return getSQLResult(strSql);
    }

    public DataSet GetPublications()
    {

        strSql = String.Format(" select * from vm_publication ");

        return getSQLResult(strSql);
    }

    public string updatePageStatus(string filename, string status)
    {
        OracleParameter[] ops = new OracleParameter[2];
        ops[0] = new OracleParameter("i_file", OracleType.VarChar);
        ops[0].Direction = ParameterDirection.Input;
        ops[0].Value = filename;

        ops[1] = new OracleParameter("i_status", OracleType.VarChar);
        ops[1].Direction = ParameterDirection.Input;
        ops[1].Value = status;

        return runStoredProcedure("PK_MARCO.updatePageStatus", ops);
    }


    private string ReplaceNewsCode(string strdate, string code)
    {
        OracleParameter[] ops = new OracleParameter[3];

        ops[0] = new OracleParameter("rc", OracleType.VarChar, 1024);
        ops[0].Direction = ParameterDirection.Output;
        ops[0].Value = "";

        ops[1] = new OracleParameter("i_date", OracleType.VarChar, 20);
        ops[1].Direction = ParameterDirection.Input;
        ops[1].Value = strdate;// dtPub.ToString("MM/dd/yyyy"); 

        ops[2] = new OracleParameter("i_newcode", OracleType.VarChar, 20);
        ops[2].Direction = ParameterDirection.Input;
        ops[2].Value = code;


        return runStoredProcedureWithReturn("PK_MARCO.replaceNewscode", ops);
    }

    private string ExportAds_Xml(int pl_id, string strdate)
    {
        OracleParameter[] ops = new OracleParameter[3];

        ops[0] = new OracleParameter("rc", OracleType.VarChar, 1024);
        ops[0].Direction = ParameterDirection.Output;
        ops[0].Value = "";

        ops[1] = new OracleParameter("i_pl_id", OracleType.Number, 9);
        ops[1].Direction = ParameterDirection.Input;
        ops[1].Value = pl_id;

        ops[2] = new OracleParameter("i_date", OracleType.VarChar, 20);
        ops[2].Direction = ParameterDirection.Input;
        ops[2].Value = strdate;// dtPub.ToString("MM/dd/yyyy"); 

        return runStoredProcedureWithReturn("PK_MARCO.ExportAdsXml", ops);
    }


    public string ExportAdsXMLFile(int pl_id, string strdate, string code)
    {
        const int NEWSCODE_LEN = 5;
        strMsg = "";
        if (code == "")
        {
            strMsg = ExportAds_Xml(pl_id, strdate);
            if (strMsg.Contains("Error"))
                return "Error:" + strMsg;
        }
        else
        {
            //if (code.Length == NEWSCODE_LEN)
            //    return "";
            strMsg = ReplaceNewsCode(strdate, code);
        }



        string strSql = "";

        if (code == "")
            strSql = String.Format("select doc,target from n_advcompose where ai_date = to_date ('{0:s}','MM/dd/YYYY') and ai_pubid = {1:d}  ", strdate, pl_id);
        else
            strSql = String.Format("select doc,target from n_advcompose where ai_date = to_date ('{0:s}','MM/dd/YYYY') and instr(ai_code,'{1}')>0  ", strdate, code.Substring(0, NEWSCODE_LEN));

        string clob = "";
        string filename = "";
        int ipages = 0;

        command.CommandType = CommandType.Text;
        command.CommandText = strSql;


        try
        {
            OracleCommand cmd = new OracleCommand(strSql, connection);

            OracleDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                if (reader.IsDBNull(0))
                    continue;

                ipages++;
                clob = reader.GetOracleLob(0).Value.ToString();

                filename = reader.GetString(1);

                string folder = Path.GetDirectoryName(filename);

                // create father folder 
                if (!Directory.Exists(folder))
                {
                    // Create the directory it does not exist.
                    Directory.CreateDirectory(folder);
                }

                StreamWriter sw = File.CreateText(filename);
                sw.Write(clob);
                sw.Close();

            }
        }
        catch (Exception err)
        {
            strMsg = String.Format("Error: {0} {1:s} Writing {2} / {3}", DateTime.Now, err.Message, filename, strSql);
            Trace.WriteLine(strMsg);
            return strMsg;
        }

        if (filename == "")
            return String.Format("Warning: no ads. found for publication: {0} at {1}", pl_id,strdate);
        else
            return "OK," + filename + "<br/>" + strMsg;
    }

    private string updatePageAdIndex(string filename, string adindex)
    {
        OracleParameter[] ops = new OracleParameter[2];
        ops[0] = new OracleParameter("i_file", OracleType.VarChar);
        ops[0].Direction = ParameterDirection.Input;
        ops[0].Value = filename;

        ops[1] = new OracleParameter("i_adindex", OracleType.VarChar, 1024);
        ops[1].Direction = ParameterDirection.Input;
        ops[1].Value = adindex;

        return runStoredProcedure("PK_MARCO.updatePageAdIndex", ops);
    }


    public string updatePageIndex(string file, string adindex)
    {
        updatePageAdIndex(file, adindex);

        string strSql = "";

        strSql = String.Format("select doc,target from n_advcompose where instr(pdffile,'{0}') = 1  ", file);

        string clob = "";
        string filename = "";
        int ipages = 0;

        command.CommandType = CommandType.Text;
        command.CommandText = strSql;


        try
        {
            OracleCommand cmd = new OracleCommand(strSql, connection);

            OracleDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                if (reader.IsDBNull(0))
                    continue;

                ipages++;
                clob = reader.GetOracleLob(0).Value.ToString();

                filename = reader.GetString(1);

                string folder = Path.GetDirectoryName(filename);

                // create father folder 
                if (!Directory.Exists(folder))
                {
                    // Create the directory it does not exist.
                    Directory.CreateDirectory(folder);
                }

                StreamWriter sw = File.CreateText(filename);
                sw.Write(clob);
                sw.Close();

            }
        }
        catch (Exception err)
        {
            strMsg = err.Message;
            Trace.WriteLine(String.Format("Error: {0} {1:s} /{2}", DateTime.Now, err.Message, strSql));
            return strMsg;
        }
        return "OK," + filename + "<br/>" + strMsg;
    }


    public string updatePageRIPDate(string filename)
    {
        OracleParameter[] ops = new OracleParameter[2];

        ops[0] = new OracleParameter("rc", OracleType.VarChar,1024);
        ops[0].Direction = ParameterDirection.InputOutput;
        ops[0].Value = "";

        ops[1] = new OracleParameter("i_file", OracleType.VarChar);
        ops[1].Direction = ParameterDirection.Input;
        ops[1].Value = filename;


        return runStoredProcedureWithReturn("PK_MARCO.updatePageDate", ops);
    }


    public string updatePageComposeStatus(string filename,int icompose)
    {
        OracleParameter[] ops = new OracleParameter[2];
        ops[0] = new OracleParameter("i_file", OracleType.VarChar);
        ops[0].Direction = ParameterDirection.Input;
        ops[0].Value = filename;

        ops[1] = new OracleParameter("i_compose", OracleType.Number,9);
        ops[1].Direction = ParameterDirection.Input;
        ops[1].Value = icompose;

        return runStoredProcedure("PK_MARCO.updatePageComposeStatus", ops);
    }



    private string runStoredProcedure(string procName, OracleParameter[] procParas)
    {
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = procName;
        command.Parameters.Clear();

        if (procParas != null)
        {
            for (int i = 0; i < procParas.Length; i++)
                command.Parameters.Add(procParas[i]);
        }

        System.Data.OracleClient.OracleTransaction transaction = null;
        try
        {
            // Start a local transaction
            transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
            // Assign transaction object for a pending local transaction
            command.Transaction = transaction;

            command.ExecuteNonQuery();

            transaction.Commit();

        }
        catch (Exception error)
        {
            if (transaction != null)
                transaction.Rollback();

            strMsg = String.Format("Error:{0} {1:s} / {2}", DateTime.Now, error.Message, command.CommandText);
            Trace.WriteLine(strMsg);
            return strMsg;
        }
        return "";
    }


    private string runStoredProcedureWithReturn(string procName, OracleParameter[] procParas)
    {
        string result = "";
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = procName;
        command.Parameters.Clear();

        if (procParas != null)
        {
            for (int i = 0; i < procParas.Length; i++)
                command.Parameters.Add(procParas[i]);
        }

        System.Data.OracleClient.OracleTransaction transaction = null;

        try
        {
            // Start a local transaction
            transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
            // Assign transaction object for a pending local transaction
            command.Transaction = transaction;


            command.ExecuteNonQuery();
            result = command.Parameters[0].Value.ToString();


            transaction.Commit();
        }
        catch (Exception error)
        {
            if (transaction != null)
                transaction.Rollback();

            strMsg = String.Format("Error:{0} {1:s} / {2}", DateTime.Now, error.Message, command.CommandText);
            Trace.WriteLine(strMsg);
            return strMsg;
        }
        return result;
    }

   
   
}

